package com.zwy.Shopping.dao.impl;

import com.zwy.Shopping.connection.DBCon;
import com.zwy.Shopping.dao.ProductDao;
import com.zwy.Shopping.model.Category;
import com.zwy.Shopping.model.Product;

import java.awt.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ProductDaoImpl implements ProductDao {
    @Override
    public List<Category> getAllCategories() {
        //创建一个装category对象的列表,ArrayList,菜鸟教程Java高级教程
        List<Category> categories = new ArrayList<>();
        //获取链接
        Connection con = DBCon.getConnection();
        if (con == null) {
            System.out.println("数据库连接失败");
        }
        try {
            //构建执行语句,不带参
            Statement stmt = con.createStatement();
            String sql = "select * from category";
            ResultSet rs = stmt.executeQuery(sql);
            //循坏遍历结果集
            while (rs.next()) {
                Category category = new Category();
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                categories.add(category);
            }
            //返回Array列表
            return categories;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }

    @Override
    public Map<Integer, Integer> getProductSales() throws SQLException {
        //获得链接
        Connection con = DBCon.getConnection();
        if (con == null) {
            System.out.println("数据库连接失败");
            return null;
        }
        //构建执行语句,首先要以产品表为主表
        Statement stmt = con.createStatement();
        //1.左连接
        //select * from product left  join order_item on product.id = order_item.product_id;
        //2.不让product_id重复,分组group by,只要product.id 和对应的 sales
        //select product.id , sum(quantity) as sales from product left  join order_item on product.id = order_item.product_id group by product.id;
        //3.如果quantity为null我们将其赋值为0
        //select id,ifnull(sales,0) sales from (select product.id as id, sum(quantity) as sales from product left  join order_item on product.id = order_item.product_id group by product.id) as START ;
        //这里的as son是给子查询取的别名`没有提供别名，SQL 引擎无法识别如何引用这个子查询的结果
        String sql = "select id, ifnull(sales,0) sales from (select product.id as id, sum(quantity) as sales from product left  join order_item on product.id = order_item.product_id group by product.id) as son";
        //执行sql语句并且获取返回集
        ResultSet rs = stmt.executeQuery(sql);
        //实例化一个map对象用来接收返回集的结果,Map是接口,HashMap才是类
        Map<Integer, Integer> salesMap = new HashMap<>();
        while (rs.next()) {
            //put()用于HashMap添加数据
            salesMap.put(rs.getInt("id"), rs.getInt("sales"));
        }
        return salesMap;
    }

    @Override
    public List<Product> getAllProduct() {
        //实例化一个存放Product对象的ArrayList对象
        List<Product> products = new ArrayList<>();
        //获取数据库链接
        Connection con = DBCon.getConnection();
        //判断链接是否为空
        if (con == null) {
            System.out.println("数据库连接失败");
            return null;
        }
        //连接不为空
        //构建sql执行语句
        try {
            Statement stmt = con.createStatement();
            //编写sql语句,注意目的是从数据库中取出数据,,这里还需要category.name,所以需要将product,category表链接

            String sql = "select * from product left join category on product.category_id = category.id";
            Map<Integer, Integer> salesMap = getProductSales();

            //执行sql语句,并且获得结果集
            ResultSet rs = stmt.executeQuery(sql);
            //循环遍历返回集
            while (rs.next()) {
                //创建一个product对象接受返回集中的一条元素
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setName(rs.getString("name"));
                int categoryId = rs.getInt("category_id");
                //获取种类名称
                String categoryName = rs.getString("category.name");

                Category category = new Category();
                category.setName(categoryName);
                category.setId(categoryId);
                //category对象构建成功
                product.setCategory(category);
                product.setMarketPrice(rs.getBigDecimal("market_price"));
                product.setPrice(rs.getBigDecimal("price"));
                product.setImage(rs.getString("image"));
                product.setStock(rs.getInt("stock"));
                //销量,通过销量Map
                product.setSales(salesMap.get(rs.getInt("id")));

                //将product对象存到products数组中
                products.add(product);


            }
            //关闭资源
            stmt.close();
            rs.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        //返回获得的products数组
        return products;
    }

    @Override
    public List<Product> getProduct(int categoryId) {

        //获取链接
        Connection con = DBCon.getConnection();
        if (con == null) {
            System.out.println("数据库连接失败");
            return null;
        }
        //构造执行语句含参
        try {
            //获取销售表
            Map<Integer, Integer> salesMap = getProductSales();
            //构造Products list对象
            List<Product> products = new ArrayList<>();
            PreparedStatement pst = con.prepareStatement(
                    "select * from product " +
                            "left join category on " +
                            "product.category_id = category.id " +
                            "where category.id = ?");
            pst.setInt(1, categoryId);
            //执行sql,获取返回值
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                //创建一个product对象接受返回集中的一条元素
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setName(rs.getString("name"));
                //获取种类名称
                String categoryName = rs.getString("category.name");

                Category category = new Category();
                category.setName(categoryName);
                category.setId(categoryId);
                //category对象构建成功
                product.setCategory(category);
                product.setMarketPrice(rs.getBigDecimal("market_price"));
                product.setPrice(rs.getBigDecimal("price"));
                product.setImage(rs.getString("image"));
                product.setStock(rs.getInt("stock"));
                //销量,通过销量Map
                product.setSales(salesMap.get(rs.getInt("id")));

                //将product对象存到products数组中
                products.add(product);

            }

            //关闭资源
            pst.close();
            rs.close();

            return products;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public List<Product> getProduct(String key) {
        //获取数据库连接
        Connection con = DBCon.getConnection();
        if (con == null) {
            System.out.println("数据库连接失败");
        }
        //创建ArrayList对象存出数据库查找到的product对象
        List<Product> products = new ArrayList<>();
        //获取销量表
        try {
            Map<Integer, Integer> salesMap = getProductSales();
            //构建含参数的sql执行语句,模糊查询
            PreparedStatement pst = con.prepareStatement("select * from product join category on " +
                    "product.category_id = category.id  "
                    + "where product.name like ?" +
                    "or category.name like ?  "
            );
            String key1 = "%" + key + "%";
            pst.setString(1, key1);
            pst.setString(2, key1);
            //执行sql语句
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                //创建一个product对象接受返回集中的一条元素
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setName(rs.getString("name"));
                //获取种类名称
                String categoryName = rs.getString("category.name");
                int categoryId = rs.getInt("category.id");
                Category category = new Category();
                category.setName(categoryName);
                category.setId(categoryId);
                //category对象构建成功
                product.setCategory(category);
                product.setMarketPrice(rs.getBigDecimal("market_price"));
                product.setPrice(rs.getBigDecimal("price"));
                product.setImage(rs.getString("image"));
                product.setStock(rs.getInt("stock"));
                //销量,通过销量Map
                product.setSales(salesMap.get(rs.getInt("id")));

                //将product对象存到products数组中
                products.add(product);


            }
            //释放资源
            pst.close();
            rs.close();
            return products;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public Product getProductById(int productId) {
        Connection con = DBCon.getConnection();


        if(con == null){
            System.out.println("数据库链接失败");
            return  null;
        }
        PreparedStatement pst = null;
        try {
            Map<Integer, Integer> salesMap = getProductSales();
            pst = con.prepareStatement("select * from product left join category on product.category_id = category.id where product.id = ?");
            pst.setInt(1,productId);
            ResultSet rs = pst.executeQuery();
            Product product = new Product();
            if(rs.next()){

                product.setId(productId);
                product.setName(rs.getString("product.name"));
                product.setSales(salesMap.get(rs.getInt("product.id")));
                product.setImage(rs.getString("image"));
                product.setStock(rs.getInt("stock"));
                product.setPrice(rs.getBigDecimal("price"));
                product.setMarketPrice(rs.getBigDecimal("market_price"));
                Category category = new Category();
                category.setId(rs.getInt("category_id"));
                category.setName(rs.getString("category.name"));
                product.setCategory(category);
            }
            return product;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public boolean reduceProductStock(int productId, int reduceNumber)  {
        Connection con = null;
        int updateNumber = 0;
        PreparedStatement pst = null;
        con = DBCon.getConnection();
        if(con ==null){
            System.out.println("数据库连接失败");
        }
        try {
            pst = con.prepareStatement("update product set stock = stock - ?  where id = ?");
            pst.setInt(1,reduceNumber);
            pst.setInt(2,productId);
            updateNumber = pst.executeUpdate();
            if(updateNumber > 0){
                System.out.println("修改成功");
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return false;
    }

    @Override
    public boolean IncreaseProductStock(int productId, int increaseNumber) {
        Connection con = null;
        int updateNumber = 0;
        PreparedStatement pst = null;
        con = DBCon.getConnection();
        if(con ==null){
            System.out.println("数据库连接失败");
        }
        try {
            pst = con.prepareStatement("update product set stock = stock + ?  where id = ?");
            pst.setInt(1,increaseNumber);
            pst.setInt(2,productId);
            updateNumber = pst.executeUpdate();
            if(updateNumber > 0){
                System.out.println("修改成功");
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return false;
    }
    //管理员端(后端)----------------------------------
    public boolean add(Product pro) {
        //连接MySQL
        Connection conn = DBCon.getConnection();
        //预编译sql执行
        String sql = "insert into product(name,category_id,market_price,price,image,stock) values(?,?,?,?,?,?)";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, pro.getName());
            ps.setInt(2,pro.getCategory().getId());
            ps.setBigDecimal(3, pro.getPrice());
            ps.setBigDecimal(4,pro.getMarketPrice());
            ps.setString(5,pro.getImage());
            ps.setInt(6,pro.getStock());
            //执行操作更改
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean modifyProduct(Product pro) {
        // 获取数据库连接
        Connection conn = DBCon.getConnection();
        // 预编译 SQL 语句
        String sql = "UPDATE product SET name = ?,  market_price = ?,price = ?, stock = ?, image = ? WHERE id = ?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, pro.getName());
            ps.setBigDecimal(2, pro.getMarketPrice());
            ps.setBigDecimal(3,pro.getPrice());
            ps.setInt(4, pro.getStock());
            ps.setString(5, pro.getImage());
            ps.setInt(6,pro.getId());
            // 执行更新操作
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    public boolean deleteProduct(int productId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        boolean success = false;

        try {
            conn = DBCon.getConnection(); // 获取数据库连接

            // 创建 SQL 查询
            String sql = "DELETE FROM product WHERE id = ?";

            // 创建并执行 PreparedStatement
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, productId);
            int rowsAffected = stmt.executeUpdate();

            // 检查是否成功删除了记录
            if (rowsAffected > 0) {
                success = true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return success;
    }

    @Override
    public boolean isInOrderItem(int productId) {
        Connection con = DBCon.getConnection();
        if(con == null){
            System.out.println("数据库连接失败");
        }
        try {
            PreparedStatement pst = con.prepareStatement("select * from order_item where product_id = ?");
            pst.setInt(1,productId);
            ResultSet rs = pst.executeQuery();
            if(rs.next()){
                System.out.println("该商品存在订单信息");
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return  false;
    }
}
